﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Configuration;
namespace MBSDAL
{
  public class DataAccess
    {
        SqlConnection con=new SqlConnection();
        //SqlCommand cmd =new SqlCommand();
        //SqlParameter sp1 = new SqlParameter();
        //SqlParameter sp2 = new SqlParameter();
        //SqlParameter sp3 = new SqlParameter();
        //SqlDataAdapter daMovie ;
        //SqlCommandBuilder cb;
       // string connString;
        
      public DataSet GetMovieReport(int movie, string fromdate, string toDate)
      {
          try
          {
              string connString = ConfigurationManager.ConnectionStrings["ReportsConnString"].ConnectionString.ToString();
             // string connString = "Data Source=(local);Initial Catalog=MBS;Integrated Security=True";
              StringBuilder sbfrom = new StringBuilder("'");
              sbfrom.Append(fromdate);
              sbfrom.Append("'");
              StringBuilder sbTodate = new StringBuilder("'"); sbTodate.Append(toDate); sbTodate.Append("'");
              MovieReport dsMovieWithTitle = new MovieReport();
              SqlConnection cn = new SqlConnection(connString);
              SqlDataAdapter daMovie = new SqlDataAdapter();
              daMovie.SelectCommand = new SqlCommand("select b.Amount,m.MovieID,m.MovieTitle from Booking b join ShowTime st on st.ShowID=b.ShowTimeID join MovieSchedule ms on ms.MovieScheduleID=st.MovieScheduleID join Movie m on m.MovieID=ms.MovieID where CONVERT(datetime, CONVERT(varchar,b.bookingdate , 101))>=" + sbfrom.ToString() + " and b.bookingdate < DATEADD(dd, 1000, " + sbTodate.ToString() + ") ",cn);
              daMovie.Fill(dsMovieWithTitle.Tables[0]);
              return dsMovieWithTitle;
          }
          catch(Exception ex)
          {
              return null;
          }
      }
//Food Revenue Report
      public DataSet GetFoodCategoryWiseRep(string fromdate, string toDate)
      {
          try
          {
               string connString = ConfigurationManager.ConnectionStrings["ReportsConnString"].ConnectionString.ToString();
            //  string connString = "Data Source=(local);Initial Catalog=MBS;Integrated Security=True";
               StringBuilder sbfrom = new StringBuilder("'");
               sbfrom.Append(fromdate);
               sbfrom.Append("'");
               StringBuilder sbTodate = new StringBuilder("'"); sbTodate.Append(toDate); sbTodate.Append("'");
              FoodReport dsFood = new FoodReport();
             
              SqlConnection cn = new SqlConnection(connString);
              SqlDataAdapter daMovie = new SqlDataAdapter();
              daMovie.SelectCommand = new SqlCommand("select f.category,fo.amount from food f join foodorderitem fi on fi.foodid=f.foodid join foodorder fo on fo.orderid=fi.orderid join booking bk on bk.bookingid=fo.bookingid", cn);
             // daMovie.SelectCommand=new SqlCommand("select f.category,fo.amount from food f join foodorderitem fi on fi.foodid=f.foodid join foodorder fo on fo.orderid=fi.orderid join booking bk on bk.bookingid=fo.bookingid where CONVERT(datetime, CONVERT(varchar, b.bookingdate, 101))>=" + sbfrom.ToString() + " and b.bookingdate < DATEADD(dd, 1000, " + sbTodate.ToString() + ")",cn);
              daMovie.Fill(dsFood.Tables[0]);
             
              return dsFood;
          }
          catch (Exception ex)
          {
              return null;
          }
      }
      //Genre Wise Report
      public DataSet GetMovieGenreWiseReport(string fromdate, string toDate)
      {
          try
          {
              string connString = ConfigurationManager.ConnectionStrings["ReportsConnString"].ConnectionString.ToString();
              //  string connString = "Data Source=(local);Initial Catalog=MBS;Integrated Security=True";
              StringBuilder sbfrom = new StringBuilder("'");
              sbfrom.Append(fromdate);
              sbfrom.Append("'");
              StringBuilder sbTodate = new StringBuilder("'"); sbTodate.Append(toDate); sbTodate.Append("'");
              GenreWiseReport dsGenre = new GenreWiseReport();
              SqlConnection cn = new SqlConnection(connString);
              SqlDataAdapter daMovieGenre = new SqlDataAdapter();
              //daMovieGenre.SelectCommand = new SqlCommand("select (b.Amount),m.Genre from Booking b join ShowTime st on st.ShowID=b.ShowTimeID join MovieSchedule ms on ms.MovieScheduleID=st.MovieScheduleID join Movie m on m.MovieID=ms.MovieID and b.bookingdate between"+fromdate+" and "+toDate+"", cn);
             // daMovieGenre.SelectCommand = new SqlCommand("select (b.Amount),m.Genre from Booking b join ShowTime st on st.ShowID=b.ShowTimeID join MovieSchedule ms on ms.MovieScheduleID=st.MovieScheduleID join Movie m on m.MovieID=ms.MovieID where CONVERT(datetime, CONVERT(varchar, b.bookingdate, 101))>=" + sbfrom.ToString() + " and b.bookingdate < DATEADD(dd, 1000, " + sbTodate.ToString() + ")", cn);
              daMovieGenre.SelectCommand = new SqlCommand("select (b.Amount),m.Genre from Booking b join ShowTime st on st.ShowID=b.ShowTimeID join MovieSchedule ms on ms.MovieScheduleID=st.MovieScheduleID join Movie m on m.MovieID=ms.MovieID ", cn);
              daMovieGenre.Fill(dsGenre.Tables[0]);
              return dsGenre;
          }
          catch (Exception ex)
          {
              return null;
          }
      }
    }
}
